﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using TCL.EO;
using TCL.FUNC;
using System.Diagnostics;
using System.Threading.Tasks;
using System.Collections.Generic;

namespace TCL.DB
{
    public class SQLiteDataProvider
    {
        #region "Properties"
        public virtual Boolean bWriteLog { get; set; }
        public virtual String sConnectionStrings { get; set; }
        #endregion

        #region DB Access Functions
        protected void SetConnection(ref SQLiteCommand cmdSqLite)
        {
            SQLiteConnection connSqLite = new SQLiteConnection(ConfigurationManager.ConnectionStrings[sConnectionStrings].ConnectionString);
            if (StringEx.CompareEx(connSqLite.ConnectionString, "localhost"))
                connSqLite.ConnectionString = connSqLite.ConnectionString.Replace("localhost", SystemEx.getAppSettings("NMT_RawUrl_RootPath", System.IO.Directory.GetCurrentDirectory() + "\\"));
            if (connSqLite.State != ConnectionState.Open)
            {
                connSqLite.Open();
            }
            cmdSqLite.Connection = connSqLite;
        }

        protected void Convert_SqlCommand_SQLiteCommand(SqlCommand cmdSql, ref SQLiteCommand cmdSqLite)
        {
            cmdSqLite.CommandText = cmdSql.CommandText;
            cmdSqLite.CommandType = cmdSql.CommandType;
            foreach (SqlParameter item in cmdSql.Parameters)
            {
                cmdSqLite.Parameters.Add(new SQLiteParameter(item.ParameterName, item.Value));
            }
            SetConnection(ref cmdSqLite);
        }

        public DataSet GetDataSet(SqlCommand cmdSql)
        {
            SQLiteCommand cmdSqLite = new SQLiteCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_SELECT);
            try
            {
                Convert_SqlCommand_SQLiteCommand(cmdSql, ref cmdSqLite);
                using (DataSet ds = new DataSet())
                {
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter())
                    {
                        da.SelectCommand = cmdSqLite;
                        da.Fill(ds);
                        objLog.bStatus = true;
                        return ds;
                    }
                }
            }
            finally
            {
                if (cmdSqLite.Connection.State == ConnectionState.Open)
                    cmdSqLite.Connection.Close();
                cmdSqLite.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        public DataTable GetDataTable(SqlCommand cmdSql)
        {
            return GetDataSet(cmdSql).Tables[0];
        }

        public SQLiteDataReader GetReadOnlyData(SqlCommand cmdSql)
        {
            SQLiteCommand cmdSqLite = new SQLiteCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_OTHER);
            try
            {
                Convert_SqlCommand_SQLiteCommand(cmdSql, ref cmdSqLite);
                objLog.bStatus = true;
                return cmdSqLite.ExecuteReader();
            }
            finally
            {
                if (cmdSqLite.Connection.State == ConnectionState.Open)
                    cmdSqLite.Connection.Close();
                cmdSqLite.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        public Boolean ExecuteNonQuery(SqlCommand cmdSql)
        {
            Int32 iRet = 0;
            SQLiteCommand cmdSqLite = new SQLiteCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_INSERT_UPDATE_DELETE);
            try
            {
                Convert_SqlCommand_SQLiteCommand(cmdSql, ref cmdSqLite);
                iRet = cmdSqLite.ExecuteNonQuery();
                objLog.bStatus = true;
            }
            finally
            {
                if (cmdSqLite.Connection.State == ConnectionState.Open)
                    cmdSqLite.Connection.Close();
                cmdSqLite.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
            return (iRet >= 0 ? true : false);
        }

        public object ExecuteScalar(SqlCommand cmdSql)
        {
            SQLiteCommand cmdSqLite = new SQLiteCommand();
            LogEO objLog = new LogEO(cmdSql, sConnectionStrings, ConstEx.Log_iType.DATABASE_OTHER);
            try
            {
                Convert_SqlCommand_SQLiteCommand(cmdSql, ref cmdSqLite);
                objLog.bStatus = true;
                return cmdSqLite.ExecuteScalar();
            }
            finally
            {
                if (cmdSqLite.Connection.State == ConnectionState.Open)
                    cmdSqLite.Connection.Close();
                cmdSqLite.Dispose();
                if (bWriteLog)
                    (new Logs()).WriteLog_To_DB(objLog, true);
            }
        }

        protected Boolean SplitTable(String sTableName)
        {
            String sQuery = String.Format(ConstEx.QuerySqlite.Database_Split_Table, sTableName + DateTime.Now.ToString("_yyyyMMdd_HHmmss"), sTableName, sTableName, sTableName);
            SqlCommand cmd = new SqlCommand(sQuery);
            cmd.CommandType = CommandType.Text;
            return ExecuteNonQuery(cmd);
        }

        protected Boolean Database_ResetAutoincrement_Table(String sTableName)
        {
            String sQuery = String.Format(ConstEx.QuerySqlite.Database_ResetAutoincrement_Table, sTableName);
            SqlCommand cmd = new SqlCommand(sQuery);
            cmd.CommandType = CommandType.Text;
            return ExecuteNonQuery(cmd);
        }

        protected Boolean SaveDataTable(DataTable DT)
        {
            Boolean bRet = false;
            try
            {
                SQLiteConnection con = new SQLiteConnection();
                con.Open();
                SQLiteCommand cmd = con.CreateCommand();
                cmd.CommandText = string.Format("SELECT * FROM {0}", DT.TableName);
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
                adapter.Update(DT);
                con.Close();
                bRet = true;
            }
            catch (Exception Ex)
            {
                bRet = false;
            }
            return bRet;
        }

        public DateTime Get_DateTime_Now()
        {
            DateTime tRet = new DateTime();
            SQLiteCommand cmdSqLite = new SQLiteCommand();
            try
            {
                cmdSqLite.CommandText = "SELECT datetime('now', 'localtime');";
                cmdSqLite.CommandType = CommandType.Text;
                SetConnection(ref cmdSqLite);
                tRet = Convert.ToDateTime(cmdSqLite.ExecuteScalar());
            }
            catch
            {
                tRet = DateTime.Now;
            }
            finally
            {
                if (cmdSqLite.Connection.State == ConnectionState.Open)
                    cmdSqLite.Connection.Close();
                cmdSqLite.Dispose();
            }
            return tRet;
        }
        #endregion
    }

    public class SqliteBulkCopy
    {
        public String Insert_UsingTran_1(List<String> lst)
        {
            String sRet = "";
            try
            {
                // Creates new sqlite database if it is not found
                //String sQuery = String.Format("CREATE TABLE IF NOT EXISTS tblTran_1 (INTEGER  PRIMARY KEY AUTOINCREMENT, sGuid TEXT);");
                //SqlCommand cmd1 = new SqlCommand(sQuery);
                //cmd1.CommandType = CommandType.Text;
                //(new SQLiteDataProvider()).ExecuteNonQuery(cmd1);


                var stopwatch = new Stopwatch();
                stopwatch.Start();
                using (var conn = new SQLiteConnection(@"Data Source=C:\BulkCopy.sqlite"))
                {
                    // Be sure you already created the Person Table!
                    conn.Open();

                    for (int i = 0; i < lst.Count - 1; i++)
                    {
                        using (var cmd = new SQLiteCommand(conn))
                        {
                            cmd.CommandText = String.Format("INSERT INTO tblTran_1 (sGuid) VALUES ('{0}');", lst[i]);
                            cmd.ExecuteNonQuery();
                        }
                    }
                    conn.Close();
                }
                sRet = String.Format("{0} seconds with one transaction.", stopwatch.Elapsed.TotalSeconds);

                //using (var conn = new SQLiteConnection(@"Data Source=C:\BulkCopy.sqlite"))
                //{
                //    // Be sure you already created the Person Table!
                //    conn.Open();
                //    var stopwatch = new Stopwatch();
                //    stopwatch.Start();
                //    using (var cmd = new SQLiteCommand(conn))
                //    {
                //        using (var transaction = conn.BeginTransaction())
                //        {
                //            // 100,000 inserts
                //            for (var i = 0; i < lst.Count - 1; i++)
                //            {
                //                cmd.CommandText = String.Format("INSERT INTO tblTran_1 (sGuid) VALUES ('{0}');", lst[i]);
                //                cmd.ExecuteNonQuery();
                //            }
                //            transaction.Commit();
                //        }
                //    }
                //    sRet = String.Format("{0} seconds with one transaction.", stopwatch.Elapsed.TotalSeconds);
                //    conn.Close();
                //}
            }
            catch (Exception ex)
            {
                sRet = ex.ToString();
            }
            return sRet;
        }

        public String Insert_UsingTran_2(List<String> lst)
        {
            String sRet = "";
            try
            {
                // Creates new sqlite database if it is not found
                //String sQuery = String.Format("CREATE TABLE IF NOT EXISTS tblTran_2 (INTEGER  PRIMARY KEY AUTOINCREMENT, sGuid TEXT);");
                //SqlCommand cmd1 = new SqlCommand(sQuery);
                //cmd1.CommandType = CommandType.Text;
                //(new SQLiteDataProvider()).ExecuteNonQuery(cmd1);

                using (var conn = new SQLiteConnection(@"Data Source=C:\BulkCopy.sqlite;Version=3;"))
                {
                    // Be sure you already created the Person Table!
                    //conn.PoolCount = 100;
                    conn.BusyTimeout = 100;
                    //conn.ConnectionTimeout = 100;
                    conn.DefaultTimeout = 100;
                    conn.Open();
                    var stopwatch = new Stopwatch();
                    stopwatch.Start();
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        using (var transaction = conn.BeginTransaction())
                        {
                            // 100,000 inserts
                            Parallel.ForEach(lst, (item) =>
                            {
                                cmd.CommandText = String.Format("INSERT INTO tblTran_2 (sGuid) VALUES ('{0}');", item);
                                cmd.ExecuteNonQuery();
                            });
                            //for (var i = 0; i < 1000000; i++)
                            //{
                            //    cmd.CommandText = String.Format("INSERT INTO tblTran (sGuid) VALUES ('{0}');", lst[i]);
                            //    cmd.ExecuteNonQuery();
                            //}
                            transaction.Commit();
                        }
                    }
                    sRet = String.Format("{0} seconds with one transaction.", stopwatch.Elapsed.TotalSeconds);
                    conn.Close();
                }
            }
            catch (Exception ex)
            {
                sRet = ex.ToString();
            }
            return sRet;
        }
    }
}




//public int DBSize()
//{
//    using (SQLiteCommand cmd = new SQLiteCommand("select sum(size) * 8 * 1024 from sysfiles"))
//    {
//        cmd.CommandType = CommandType.Text;
//        return (int)ExecuteScalar(cmd);
//    }
//}

//public DataTable getDatabaseSizeCurrent(String _DATABASE_NAME)
//{
//    DataTable dt = new DataTable();
//    SQLiteCommand cmd = new SQLiteCommand("getDatabaseSizeCurrent", connSQLite);
//    cmd.CommandType = CommandType.StoredProcedure;
//    //cmd.Parameters.Add(new SqlParameter("@DATABASE_NAME", _DATABASE_NAME));
//    return GetDataTable(cmd);
//}

//public bool CheckConnect()
//{
//    SQLiteCommand cmd = new SQLiteCommand("select getdate()");
//    if (DataTableEx.DataTable_OK(GetDataTable(cmd)))
//        return true;
//    else
//        return false;
//}

//public Boolean Backup_Database(String LinkBackup)
//{
//    SQLiteCommand cmd = new SQLiteCommand("BACKUP DATABASE [NguyenManhThang] TO DISK='" + LinkBackup + "'", connSQLite);
//    ExecuteNonQuery(cmd);
//    return true;
//}

//public Boolean Restore_Database(String LinkRestore)
//{
//    SQLiteCommand cmd = new SQLiteCommand("USE MASTER RESTORE DATABASE [NguyenManhThang] FROM DISK='" + LinkRestore + "'", connSQLite);
//    ExecuteNonQuery(cmd);
//    return true;
//}


//public static void Test()
//{
//    String url = System.Web.HttpContext.Current.Server.MapPath("~/GlobalResources/ThangNM.sqlite");
//    String abc = "";
//    m_dbConnection = new SQLiteConnection("Data Source=" + url + ";Version=3;");
//    m_dbConnection.Open();

//    string sql = "select * from tblError order by PK_iError desc";
//    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
//    SQLiteDataReader reader = command.ExecuteReader();
//    while (reader.Read())
//    {
//        abc = abc + "PK_iError: " + reader["PK_iError"] + "\tsContent: " + reader["sContent"];
//    }
//    m_dbConnection.Close();

//    throw new System.ArgumentException(abc);

//}






////if the database has already password
//try{
//            string conn = @"Data Source=database.s3db;Password=Mypass;";
//            SQLiteConnection connection= new SQLiteConnection(conn);
//            connection.Open();
//            //Some code
//            connection.ChangePassword("Mypass");
//            connection.Close();
//    }
////if it is the first time sets the password in the database
//catch
//    {
//            string conn = @"Data Source=database.s3db;";
//            SQLiteConnection connection= new SQLiteConnection(conn);
//            connection.Open();
//            //Some code
//            connection.ChangePassword("Mypass");
//            connection.Close();
//    }